const express = require('express'),
  router = express.Router(),

  xlsx = require('node-xlsx');

/**
 * @function 初始化添加会员数据(用户注册成功时调用)
 * @author siwei
 * @date 2018-07-24
 * @param uuid,cardstatus,committee,duration,cardno
 */
router.post('/add', function (req, res) {
  let obj = {}
  let uuid = req.body.uuid
  let uname = req.body.uname
  let cardstatus = req.body.cardstatus || '0'
  let applytype = req.body.applytype || ''
  let committee = req.body.committee || ''
  let duration = req.body.duration || ''
  let reason = req.body.reason || ''
  let cardno = req.body.cardno || ''

  let params = {
    uuid: uuid,
    uname: uname,
    cardstatus: cardstatus,
    applytype: applytype,
    committee: committee,
    duration: duration,
    reason: reason,
    cardno: cardno,
  }
  if (uuid && uname) {

    if (uuid.length > 40 || uname.length > 30 || cardstatus.length > 1 || applytype.length > 1 || committee.length > 50 || duration.length > 40 || reason.length > 100 || cardno.length > 30) {
      obj.return = '-1'
      obj.code = '-1'
      obj.message = '请求参数长度超出限制'
      res.send(obj)
    } else {

      gps_vip.create(params).then(record => {
        obj.return = '0'
        obj.code = '0'
        obj.message = '添加会员成功'
        obj.data = record
        res.send(obj)
      }).catch(err => {
        obj.return = '-1'
        obj.code = '-1'
        obj.message = '添加会员失败'
        obj.data = err
        res.send(obj)
      });
    }
  } else {
    obj.return = '-1'
    obj.code = '-1'
    obj.message = '这些参数不能为空(uuid,uname)'
    res.send(obj)
  }

})

/**
 * @function 更新会员申请信息(用户提交申请/会员审核状态发生改变时调用)
 * @author siwei
 * @date 2018-07-24
 * @param uuid,cardstatus,applytype,committee,duration,reason,cardno
 */
router.post('/update', function (req, res) {
  let obj = {}
  let uuid = req.body.uuid
  let isfirstsubmit = req.body.isfirstsubmit || false;
  if (isfirstsubmit) {
    //如果是第一次提交,则把状态设为已提交申请,待初审1
    var cardstatus = req.body.cardstatus || '1'
  } else {
    //否则就按传过来的状态参数进行更新
    var cardstatus = req.body.cardstatus || ''
  }
  // 如果会员状态为xx,则生成会员编号,否则清空会员编号

  let applytype = req.body.applytype || ''
  let committee = req.body.committee || ''
  let duration = req.body.duration || ''
  let reason = req.body.reason || ''
  let cardno = req.body.cardno || ''

  let updateparams = {
    cardstatus: cardstatus,
    applytype: applytype,
    committee: committee,
    duration: duration,
    reason: reason,
    cardno: cardno,
  }

  //去掉空的属性
  for (var key of Object.keys(updateparams)) {
    if (!updateparams[key] && key != 'duration')
      delete updateparams[key]
  }

  if (!uuid) {
    obj.return = '-1'
    obj.code = '-1'
    obj.message = 'uuid不能为空'
    res.send(obj)
  } else {

    if ((cardstatus == '3' || cardstatus == '5') && !duration) {   //审核通过时入会期限不能为空
      obj.return = '-1'
      obj.code = '-1'
      obj.message = '审核通过时入会期限不能为空!'
      res.send(obj)
    } else {
      gps_vip.update(updateparams, {where: {uuid: uuid}}).then(record => {
        if (record[0] > 0) {
          obj.return = '0'
          obj.code = '0'
          obj.message = '修改会员成功'
          obj.data = record[0]
        } else {
          obj.return = '-1'
          obj.code = '-1'
          obj.message = '无效的用户id'
          obj.data = []
        }
        res.send(obj)
      }).catch(err => {
        obj.return = '-1'
        obj.code = '-1'
        obj.message = '修改会员失败'
        obj.data = err
        res.send(obj)
      });
    }
  }
})

/**
 * @function 删除对应的会员数据(成功删除用户之后调用)
 * @author siwei
 * @date 2018-07-24
 * @param uuid
 */
router.post('/del', function (req, res) {
  let obj = {}
  let params = {
    uuid: req.body.uuid
  }

  gps_vip.destroy({where: params}).then(record => {
    if (record > 0) {
      obj.return = '0'
      obj.code = '0'
      obj.message = '删除会员成功'
      obj.data = record
    } else {
      obj.return = '-1'
      obj.code = '-1'
      obj.message = '删除会员失败或此记录不存在'
      obj.data = record
    }
    res.send(obj)
  }).catch(err => {
    obj.return = '-1'
    obj.code = '-1'
    obj.message = '删除会员失败'
    obj.data = err
    res.send(obj)
  });
})

/**
 * @function 获取会员信息
 * @author siwei
 * @date 2018-07-24
 * @param uuid
 */
router.post('/getvipinfo', function (req, res) {
  let obj = {}
  let params = {}
  let uuid = req.body.uuid
  if (uuid) {
    params.uuid = uuid;

    gps_vip.findOne({where: params}).then(record => {
      if (record) {
        obj.return = '0'
        obj.code = '0'
        obj.message = '获取会员信息成功'
        obj.data = record
      } else {
        obj.return = '-1'
        obj.code = '-1'
        obj.message = '获取会员信息失败或此记录不存在'
        obj.data = {}
      }
      res.send(obj)
    }).catch(err => {
      obj.return = '-1'
      obj.code = '-1'
      obj.message = '获取会员信息失败'
      obj.data = err
      res.send(obj)
    });
  } else {
    obj.return = '-1'
    obj.code = '-1'
    obj.message = 'uuid不能为空'
    obj.data = []
    res.send(obj)
  }
})

/**
 * @function 获取所有会员数据(支持分页)
 * @author siwei
 * @date 2018-07-24
 * @param page,pagerow
 */
/*router.post('/getviplist_all', function (req, res) {
  let obj = {}
  let params = {}
  let cardstatus = req.body.cardstatus || ''
  if (cardstatus) {
    params.cardstatus = {'$ne': 0, '$eq': cardstatus}
  } else {
    params.cardstatus = {'$ne': 0}
  }
  let pagerow = parseInt(req.body.pagerow)
  let page = parseInt(req.body.page)
  //对页码进行容错
  if (pagerow > 999) {
    pagerow = 999
  }
  if (page == 0) {
    page = 1
  }

  gps_vip.findAndCountAll({
    order: "updatetime DESC",
    limit: pagerow || 100,
    offset: parseInt(pagerow * (page - 1)) || 0,
    where: params
  }).then(record => {
    obj.return = '0'
    obj.code = '0'
    obj.message = '获取所有会员列表成功'
    obj.rowcount = record.count
    obj.data = record.rows
    res.send(obj)
  }).catch(err => {
    obj.return = '-1'
    obj.code = '-1'
    obj.message = '获取所有会员列表失败'
    obj.data = err
    res.send(obj)
  });
})*/

/**
 * @function 会员条件限制查询(专业委员会/用户名,支持分页)
 * @author siwei
 * @date 2018-07-24
 * @param uname,committee,cardstatus,duration,pagerow,page
 */
router.post('/getvipbyfilter', function (req, res) {
  let obj = {}
  let uname = req.body.uname || ''
  let committee = req.body.committee || ''
  let cardstatus = req.body.cardstatus || ''
  let duration = req.body.duration || ''
  let params = {}
  let pagerow = parseInt(req.body.pagerow)
  let page = parseInt(req.body.page)
  //对页码进行容错
  if (pagerow > 999) {
    pagerow = 999
  }
  if (page == 0) {
    page = 1
  }
  if (uname) {
    params.uname = {'$like': '%' + uname + '%'}
  }
  if (committee) {
    params.committee = committee
  }
  if (cardstatus) {
    if (cardstatus == '{$ne:0}') {
      params.cardstatus = {'$ne': 0}
    } else {
      params.cardstatus = cardstatus
    }
  }
  // if (duration) {
  //   params.duration = duration
  // }

  gps_vip.findAndCountAll({
    order: "updatetime DESC",
    limit: pagerow || 100,
    offset: parseInt(pagerow * (page - 1)) || 0,
    where: params
  }).then(record => {
    obj.return = '0'
    obj.code = '0'
    obj.message = '根据条件获取会员列表成功'
    obj.rowcount = record.count
    obj.data = record.rows
    res.send(obj)
  }).catch(err => {
    obj.return = '-1'
    obj.code = '-1'
    obj.message = '根据条件获取会员列表失败'
    obj.data = err
    res.send(obj)
  });
})

/**
 * @function 批量审核会员申请(分初审和终审)
 * @author siwei
 * @date 2018-07-25
 * @param uuid,cardstatus,duration,reason
 */
router.post('/batchaudit', function (req, res) {
  let obj = {}
  let uuidArr = req.body.uuid.split(',');
  let type = req.body.type;       //1为初审,2为终审
  let status = req.body.status;   //true为通过,false为不通过
  let duration = req.body.duration;
  let reason = req.body.reason || '';

  let cardstatus;

  if (type == undefined || status == undefined) {
    obj.return = '-1'
    obj.code = '-1'
    obj.message = 'type和status不能为空'
    res.send(obj)
  } else {
    if (status && !duration) {
      obj.return = '-1'
      obj.code = '-1'
      obj.message = '审核通过时入会期限不能为空!'
      res.send(obj)
    } else {
      if (type == '1') {
        cardstatus = status ? '3' : '2';
      } else if (type == '2') {
        cardstatus = status ? '5' : '4';
      }
      let params = {
        uuid: uuidArr
      }
      let updateparams = {
        cardstatus: cardstatus,
        duration: duration,
        reason: reason
      }
      gps_vip.update(updateparams, {where: params}).then(record => {
        obj.return = '0'
        obj.code = '0'
        obj.message = record[0] + '个会员审核成功'
        obj.data = record[0]
        res.send(obj)
      }).catch(err => {
        obj.return = '-1'
        obj.code = '-1'
        obj.message = '会员审核失败'
        obj.data = err
        res.send(obj)
      });
    }
  }
})

/**
 * @function 导出会员信息为xls格式
 * @author siwei
 * @date 2018-07-27
 * @param cardstatus,committee
 */
router.get('/exportxlsx', function (req, res) {
  let obj = {}
  let sequelize = DbInit.sequelize
  let cardstatus = req.query.cardstatus
  let committee = req.query.committee

  let sql = "SELECT " +
    "gps_user.uuid," +
    "gps_user.uname," +
    "gps_vip.cardno," +
    "gps_vip.committee," +
    "gps_vip.duration," +
    "gps_vip.cardstatus," +
    "gps_user.name," +
    "gps_user.utype," +
    "gps_user.unickname," +
    "gps_user.sex," +
    "gps_user.nativeplace," +
    "gps_user.birthday," +
    "gps_user.education," +
    "gps_user.credentialstype," +
    "gps_user.credentialsno," +
    "gps_user.company," +
    "gps_user.techtitle," +
    "gps_user.jobtitle," +
    "gps_user.people," +
    "gps_user.polity," +
    "gps_user.graduatedschool," +
    "gps_user.province," +
    "gps_user.addr," +
    "gps_user.zipcode," +
    "gps_user.ucomment," +
    "gps_user.status," +
    "gps_user.uicon," +
    "gps_user.mail," +
    "gps_user.tel," +
    "gps_user.qq," +
    "gps_user.techbackground," +
    "gps_user.workexperience," +
    "gps_user.eduexperience," +
    "gps_user.achievement" +
    " FROM " +
    "gps_user," +
    "gps_vip" +
    " WHERE " +
    "gps_vip.uuid=gps_user.uuid";
  if (cardstatus) {
    sql += " AND gps_vip.cardstatus=" + cardstatus + ""
  }
  if (committee) {
    sql += " AND gps_vip.committee='" + committee + "'"
  }

  sequelize.query(sql, {
    type: sequelize.QueryTypes.SELECT
  }).then(record => {
    if (record.length > 0) {

      title = ["用户编码", "用户名", "会员编号", "委员会", "入会期限", "会员状态", "姓名", "用户类型", "昵称", "性别", "籍贯", "出生日期", "学历", "证件类型", "证件号码", "工作单位", "专业技术职称", "单位职务", "民族", "政党", "毕业院校", "所在区域", "通讯地址", "邮政编码", "备注信息", "用户状态", "用户头像", "电子邮箱", "手机号码", "qq号码", "专业背景", "工作经历", "教育经历", "主要成就"]
      let csv_result = []
      csv_result.push(title)
      var options = {}
      options.defaultHAlign = 'center'
      options.defaultVAlign = 'middle'
      options.forcedColWidth = [22, 100]
      record.forEach(function (item, index) {
        let tmp_csv = []
        tmp_csv.push(item.uuid || ' ')
        tmp_csv.push(item.uname || ' ')
        tmp_csv.push(item.cardno || ' ')
        tmp_csv.push(item.committee || ' ')
        tmp_csv.push(item.duration || ' ')
        tmp_csv.push(item.cardstatus || ' ')
        tmp_csv.push(item.name || ' ')
        tmp_csv.push(item.utype || ' ')
        tmp_csv.push(item.unickname || ' ')
        tmp_csv.push(item.sex || ' ')
        tmp_csv.push(item.nativeplace || ' ')
        tmp_csv.push(item.birthday || ' ')
        tmp_csv.push(item.education || ' ')
        tmp_csv.push(item.credentialstype || ' ')
        tmp_csv.push(item.credentialsno || ' ')
        tmp_csv.push(item.company || ' ')
        tmp_csv.push(item.techtitle || ' ')
        tmp_csv.push(item.jobtitle || ' ')
        tmp_csv.push(item.people || ' ')
        tmp_csv.push(item.polity || ' ')
        tmp_csv.push(item.graduatedschool || ' ')
        tmp_csv.push(item.province || ' ')
        tmp_csv.push(item.addr || ' ')
        tmp_csv.push(item.zipcode || ' ')
        tmp_csv.push(item.ucomment || ' ')
        tmp_csv.push(item.status || ' ')
        tmp_csv.push(item.uicon || ' ')
        tmp_csv.push(item.mail || ' ')
        tmp_csv.push(item.tel || ' ')
        tmp_csv.push(item.qq || ' ')
        tmp_csv.push(item.techbackground || ' ')
        tmp_csv.push(item.workexperience || ' ')
        tmp_csv.push(item.eduexperience || ' ')
        tmp_csv.push(item.achievement || ' ')
        // tmp_csv.push(moment(item.birthday).format('YYYY-MM-DD') || ' ')

        csv_result.push(tmp_csv)
      })
      buffer = xlsx.build([{name: "viplist", data: csv_result}]);
      res.set('Content-Disposition', 'attachment; filename=viplist.xlsx')
      res.send(buffer)

    } else {
      obj.return = '0'
      obj.code = '0'
      obj.message = '没有符合条件的数据'
      obj.data = []
      res.send(obj)
    }
  }).catch(err => {
    obj.return = '-1'
    obj.code = '-1'
    obj.message = '获取数据出错'
    obj.data = err
    res.send(obj)
  })
})

/**
 * @function 查询指定状态的所有会员名单(包含用户部分字段,如name、)
 * @author siwei
 * @date 2018-07-31
 * @param cardstatus,committee
 */
router.post('/getviplist_withuser', function (req, res) {
  let obj = {}
  let sequelize = DbInit.sequelize
  let cardstatus = req.body.cardstatus
  let committee = req.body.committee

  let sql = "SELECT " +
    "gps_user.uuid," +
    "gps_user.uname," +
    "gps_user.name," +
    "gps_vip.cardno," +
    "gps_vip.committee," +
    "gps_vip.applytype," +
    "gps_vip.duration," +
    "gps_vip.cardstatus" +
    " FROM " +
    "gps_user," +
    "gps_vip" +
    " WHERE " +
    "gps_vip.uuid=gps_user.uuid";
  if (cardstatus) {
    sql += " AND gps_vip.cardstatus='" + cardstatus + "'"
  }
  if (committee) {
    sql += " AND gps_vip.committee='" + committee + "'"
  }

  sequelize.query(sql, {
    type: sequelize.QueryTypes.SELECT
  }).then(record => {
    if (record.length > 0) {
      obj.return = '0'
      obj.code = '0'
      obj.message = '获取会员名单成功'
      obj.rowcount = record.length
      obj.data = record
      res.send(obj)
    } else {
      obj.return = '0'
      obj.code = '0'
      obj.message = '没有符合条件的数据'
      obj.data = []
      res.send(obj)
    }
  }).catch(err => {
    obj.return = '-1'
    obj.code = '-1'
    obj.message = '获取会员名单失败'
    obj.data = err
    res.send(obj)
  })
})


module.exports = router;
